package com.example.world.dao;

import com.example.world.domain.*;
import com.example.world.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CityDAO {

    // 根据code查询国家
    public City findByCountryCode(String countryCode) throws SQLException {
        // 1. 连接
        Connection conn = JDBCUtils.getConnection();
        // 2.
        String sql = "select * from country where countrycode=?";
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, countryCode);
        // 3.
        ResultSet rs = stmt.executeQuery();
        City c = null;

        if (rs.next()) {
            String name = rs.getString("Name");
            String district = rs.getString("District");
            c = new City(countryCode, name, district);
        }

        return c;
    }


    // 查询所有
    public List<City> findAll() throws SQLException {
        // 1. 连接
        Connection conn = JDBCUtils.getConnection();
        // 2.
        String sql = "select * from city";
        PreparedStatement stmt = conn.prepareStatement(sql);
        // 3.
        ResultSet rs = stmt.executeQuery();
        List<City> list = new ArrayList<>();
        while (rs.next()) {
            String name = rs.getString("Name");

            String countryCode = rs.getString("CountryCode");
            String district = rs.getString("District");
            City c = new City(name, countryCode, district);

            list.add(c);
        }

        return list;
    }
}


